Microsoft SQL SERVER is a comprehensive database management platform that provides extensive management and development tools, a powerful extraction, transformation, and loading (ETL) tool, business intelligence and analysis services, and other capabilities. Two improvements to SQL SERVER have recently been implemented. First, the Microsoft Windows .NET Framework Common Language Runtime (CLR) has been integrated into the SQL SERVER database, and second, a new object, referred to as a user defined type (UDT), can now be created with managed code in the CLR environment and persisted in the database store.
The CLR is the heart of the Microsoft .NET Framework, and provides the execution environment for all NET code. Thus, code that runs within the CLR is referred to as “managed code.” The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management and security. The CLR is now loaded by SQL SERVER upon the first invocation of a NET routine.
In previous versions of SQL SERVER, database programmers were limited to using Transact-SQL when writing code on the server side. Transact-SQL is an extension of the Structured Query Language as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using Transact-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. Transact-SQL is specifically designed for direct structural data access and manipulation. While Transact-SQL excels at structural data access and management, it is not a full-fledged programming language in the way that Visual Basic NET and C# are. For example, Transact-SQL does not support arrays, collections, for each loops, bit shifting or classes.
With the CLR integrated into the SQL SERVER database, database developers can now perform tasks that were impossible or difficult to achieve with Transact-SQL alone. Both Visual Basic NET and C# are modern programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as Visual Basic NET and C#.
In addition to CLR integration, SQL SERVER also adds support for user defined types (UDT)—a new mechanism that enables a developer to extend the scalar type system of the database. UDTs provide two key benefits from an application architecture perspective: they provide strong encapsulation (both in the client and the server) between the internal state and the external behaviors, and they provide deep integration with other related server features. Once a UDT is defined, it can be used in all the contexts that a system type can be used in SQL SERVER, including in column definitions, variables, parameters, function results, cursors, triggers, and replication.
The process of defining a UDT on a database server is accomplished as follows:                a) create a class in managed code that follows the rules for UDT creation;        b) load the Assembly that contains the UDT into a database on the server using the CREATE ASSEMBLY statement; and        c) create a type in the database using the CREATE TYPE statement that exposes the managed code UDT.At this point, the UDT can be used in a table definition.        
When a UDT definition is created in managed code, the type must meet the following requirements:                a) it must be marked as Serializable;        b) it must be decorated with the SqlUserDefinedTypeAttribute;        c) the type should be NULL aware by implementing the INullable interface;        d) the type must have a public constructor that takes no arguments; and        e) the type should support conversion to and from a string by implementing the following methods:                    1. Public String ToString( ); and            2. Public Shared <type> Parse (SqlString s).                        
FIG. 1 is an example of a class that defines a UDT in accordance with the process described above. In accordance with the steps described above, this class is then compiled into a dynamic link library (dll). An Assembly containing the compiled class is then created using the following T-SQL script commands:                create assembly test        from ‘c:\test.dll’        go        
The following T-SQL script commands are then used to create the UDT on the server:                create type BaseItem        external name [test][BaseItem]        go        
Once the UDT has been created on the server, a table (e.g., “MyTable”) can be created defining an attribute of the table as the UDT type, as follows:
create table MyTable(Item BaseItem,ItemId as item::ID)go
A new item can be added to the table, as follows:                declare @i BaseItem        set @i=convert(BaseItem, ″)        insert into MyTable values (@i)        goThe UDT expression can then be used in a query such as: SELECT Item.ID, Item.Name FROM MyTable.        
With the integration of the CLR into SQL SERVER and the ability to define UDTs from a class definition in managed code, applications can now instantiate objects of the type defined by the managed code class and have those objects persisted in the relational database store as an instance of the UDT. Moreover, the class that defines the UDT can also include methods that implement specific behaviors on objects of that type. An application can therefore instantiate objects of a type defined as a UDT and can invoke managed behaviors over them.
When an object of a class that has been defined as a UDT is instantiated in the CLR, the object can be persisted in the database store through the process of object serialization, wherein the values of the variables of the class are transferred to physical storage (e.g., hard disk). FIG. 2 illustrates the serialization of an object in memory to its persisted form on disk. The object may be persisted in the database store in a traditional relational database table of the format illustrated in FIG. 3. As shown, the table comprises a column of the specified UDT. The serialized values of a persisted object of the specified UDT occupy a cell of the UDT column.
Referring again to FIG. 2, when an application generates a query that includes a predicate or an expression that references a managed behavior of a UDT object that has been persisted in the database store (e.g., a behavior that returns the value of a field of the UDT object), the persisted object must be de-serialized (sometimes also referred to as “hydrating”) and the CLR must allocate memory for the full object in order to receive its stored values. The CLR must then invoke the actual method that implements the behavior desired by the application. Unfortunately, the processing overhead associated with allocating memory to store the full object at run time, deserializing and populating all parts of the object, and then invoking the method that implements the requested behavior, can be burdensome. Consequently, there is a need for systems and methods that provide more efficient storage and retrieval of objects persisted in a database store. The present invention satisfies this need.